﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace DoAnOOP.DataAccessLayer
{
    class clsLyLichSV
    {
        public DataRow GetLyLichSV()
        {
            string sql = "SELECT * FROM LYLICH,KHOA WHERE LYLICH.MAKHOA  = KHOA.MAKHOA AND MSSV = \'" + frmMain.mssv + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, clsConnect.ConnectionString);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count != 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                return dr;
            } 
            else
            {
                return null;
            }
        }
        public List<DataRow> LayDanhSachKhoa()
        {
            string sql = "SELECT * FROM KHOA";
            SqlDataAdapter da = new SqlDataAdapter(sql, clsConnect.ConnectionString);
            DataSet ds = new DataSet();
            da.Fill(ds);
            List<DataRow> listDr = new List<DataRow>();
            if (ds.Tables[0].Rows.Count != 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    listDr.Add(ds.Tables[0].Rows[i]);
            }
            return listDr;
        }
        
        public bool UpdateHo(string _Ho)
        {
            string sql = "UPDATE LYLICH SET HO = '" + _Ho + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public bool UpdateTen(string _Ten)
        {
            string sql = "UPDATE LYLICH SET TEN = '" + _Ten + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        public bool UpdateNoiSinh(string _NoiSinh)
        {
            string sql = "UPDATE LYLICH SET NOISINH = '" + _NoiSinh + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        public bool UpdateEmail(string _Email)
        {
            string sql = "UPDATE LYLICH SET EMAIL = '" + _Email + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        public bool UpdateNgaySinh(string _NgaySinh)
        {
            string sql = "UPDATE LYLICH SET NGAYSINH = '" + _NgaySinh + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        public bool UpdateNgayVaoDoan(string _nvdoan)
        {
            string sql = "UPDATE LYLICH SET NGAYVAODOAN = '" + _nvdoan + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        public bool UpdateGioiTinh(string _GioiTinh)
        {
            string sql = "UPDATE LYLICH SET GIOITINH  = '" + _GioiTinh + "' WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }
        public bool UpdateKhoa(string _Khoa)
        {
            string khoa = "SELECT MAKHOA FROM KHOA WHERE TENKHOA ='" + _Khoa + "'";
            SqlDataAdapter da = new SqlDataAdapter(khoa, clsConnect.GetSqlConnect());
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataRow dr = ds.Tables[0].Rows[0];
            string sql = "UPDATE LYLICH SET MAKHOA = '" + dr["MAKHOA"].ToString() + "'WHERE MSSV = '" + frmMain.mssv + "'";
            SqlCommand command = new SqlCommand(sql, clsConnect.GetSqlConnect());
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception)
            {
                throw;
            }
        }

    }
}
